<?php
require_once $ubi['SERVER']['SERVER_ROOT'] . '/model/message.php';
require_once 'dbConnection.php';

class MessageDao {
	private $_db;
	
	public function __construct() {
		$this->_db = new DbConnection();
	}
	
	public function addMessage(MessageModel $message) {
		$query = sprintf("CALL sp_AddMessage('%s', '%s', '%u', '%u', '%u', '%u', @MessageId)", 
			$message->getTitle(),
			$message->getContent(),
			$message->getSenderId(), 
			$message->getReceiverId(), 
			$message->getStatus(),
			$message->getCreatedDate());
			
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return 0;
		}
		$result = $this->_db->executeQuery("SELECT @MessageId;");
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return 0;
		}
		
		if ($row = mysql_fetch_assoc($result)) {			
			$id = $row['@MessageId'];
		}		
				
		mysql_free_result($result);
		
		return $id;
	}
	
	public function getMessagesBySenderId($senderID) {
		$messageList = array();
		
		$query = sprintf("SELECT * FROM messages WHERE SenderId = '%u' ORDER BY DESC;", $senderID);
		
		$result = $this->_db->executeQuery($query);
				
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		while($row = mysql_fetch_assoc($result)) {			
			$message = $this->readMessageFromRow($row);			
			$messageList[] = $message;
		}
		
		mysql_free_result($result);
		
		return $messageList;
	}
	
	public function getReadMessagesByReceiverId($receiverID, $from, $to) {
		$query = sprintf("SELECT * FROM messages WHERE IsRead = 1 AND ReceiverId = '%u' ORDER BY MessageId DESC LIMIT %u, %u", 
					$receiverID, $from, $to);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		$messageList = array();
		
		while ($row = mysql_fetch_assoc($result)) {			
			$message = $this->readMessageFromRow($row);			
			$messageList[] = $message;
		}
		
		mysql_free_result($result);
		
		return $messageList;
	}
	
	public function getUnreadMessagesByReceiverId($receiverID) {
		$query = sprintf("SELECT * FROM messages WHERE IsRead = 0 AND ReceiverId = '%u' ORDER BY MessageId DESC", $receiverID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		$messageList = array();
		
		while ($row = mysql_fetch_assoc($result)) {			
			$message = $this->readMessageFromRow($row);
			$messageList[] = $message;
		}
		
		mysql_free_result($result);
		
		return $messageList;
	}
	
	public function countUnreadmessageByReceiverId($receiverID) {
		$query = sprintf("SELECT COUNT(*) FROM messages WHERE IsRead = 0 AND ReceiverId = '%u'", $receiverID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
			
		if ($row = mysql_fetch_row($result)) {			
			$nMessage = $row[0];
		}
		
		mysql_free_result($result);
		
		return $nMessage;
	}
	
	public function countReadMessages($receiverID) {
		$query = sprintf("SELECT COUNT(*) FROM messages WHERE IsRead = 1 AND ReceiverId = '%u'", $receiverID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
				
		if ($row = mysql_fetch_row($result)) {			
			$nMessage = $row[0];
		}
		
		mysql_free_result($result);
		
		return $nMessage;
	}
	
	public function readMessage($messID) {
		$query = sprintf("CALL sp_ReadMessage('%u')", $messID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
				
		return true;
	}
	
	public function deleteMessage($messID) {
		$query = sprintf("CALL sp_DeleteMessage('%u')", $messID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
				
		return true;
	}

	public function getAllMessagesByReceiverId($receiverID) {
		$query = sprintf("SELECT * FROM messages WHERE ReceiverId = '%u' ORDER BY IsRead, MessageId DESC", $receiverID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		$messageList = array();
		
		while ($row = mysql_fetch_assoc($result)) {			
			$message = $this->readMessageFromRow($row);
			$messageList[] = $message;
		}
		
		mysql_free_result($result);
		
		return $messageList;	
	}
	
	private static function readMessageFromRow($row) {
		$message = new MessageModel();
		
		$message->setMessageId($row['MessageId']);
		$message->setTitle($row['Title']);
		$message->setContent($row['Content']);
		$message->setReceiverId($row['ReceiverId']);
		$message->setSenderId($row['SenderId']);
		$message->setStatus($row['Status']);
		$message->setIsRead($row['IsRead']);
		$message->setCreatedDate($row['CreatedDate']);
		
		return $message;
	}
}
?>